package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository("ShipmentDao")
@Transactional(readOnly=false)
public class ShipmentDao {
	@Autowired	private SessionFactory sessionFactory;
	
	public List<Map> GetListPengiriman(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;            
            if (kode.equals("PENGAJUAN") || kode.equals("PERJALANAN") || kode.equals("SAMPLING")){
            	sql = "{call BBTRXPENGIRIMAN.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
                
                sql = "{call BBTRXPENGIRIMAN.SETSTATUS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("status").toString());
                call.execute();
                
                sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setBigDecimal(1, new BigDecimal(map.get("id_pltu").toString()));
                call.execute();

            }else if(kode.equals("PENGAJUAN_BY_PLTU_PEMASOK")){
            	sql = "{call BBTRXPENGIRIMAN.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setBigDecimal(1, new BigDecimal(map.get("id_pemasok").toString()));
                call.execute();
            }else if (kode.equals("PORTLIST")){
                sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
            	
            }else if (kode.equals("PENGIRIMANPORTBYPEMASOK")){		

            	System.out.println(" id pltu :" + map.get("id_pltu").toString() + " id pemasok " + map.get("id_pemasok").toString());
                sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();

                sql = "{call BBTRXPENGIRIMAN.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString());
                call.execute();
            }else if (kode.equals("PENGIRIMANPORTBYALLPEMASOK")){	
            	
            	System.out.println(" id pltu :" + map.get("id_pltu").toString()) ;// + " id pemasok " + map.get("id_pemasok").toString());
                sql = "{call BBTRXPENGIRIMAN.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
    
            }
            
            
            sql = "{? = call BBTRXPENGIRIMAN.GETLISTPENGIRIMAN(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> insertPengirimanBB(String kode, Map map) {
		List<Map> lMap = new ArrayList<Map>();
        try {
            if (kode.equals("PENGAJUAN")){     
            	lMap = insertPengirimanBB_PENGAJUAN(kode, map);
            	return lMap;
            }else if (kode.equals("PENGIRIMAN")){
            	lMap = insertPengirimanBB_PENGIRIMAN(kode, map);
            	return lMap;
            }else if (kode.equals("SAMPLING")){
            	lMap = insertPengirimanBB_SAMPLING(kode, map);
            	return lMap;
            }
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	
	private List<Map> insertPengirimanBB_PENGAJUAN(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();        
        try{
        	String sql = null;
            CallableStatement call = null;
            
            sql = "{call BBTRXPENGIRIMAN.SETETA(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("eta").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETETD(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("etd").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETID_KAPAL(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_kapal").toString()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETKETERANGAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("keterangan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETSATUAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("satuan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETSTATUS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("status").toString());
            call.execute();                
            
            sql = "{call BBTRXPENGIRIMAN.SETTANGGAL(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("tanggal").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETVOLUME(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("volume").toString()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_JADWAL(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_jadwal").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_pengajuan").toString());
            call.execute();
            
            sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}
	
	private List<Map> insertPengirimanBB_PENGIRIMAN(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        
        try{
        	String sql = null;
            CallableStatement call = null;
            
            sql = "{call BBTRXPENGIRIMAN.SETPELABUHAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("pelabuhan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETPIC(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("pic").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETKETERANGAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("keterangan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETTELP(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("telp").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETSTATUS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("status").toString());
            call.execute();                
            
            sql = "{call BBTRXPENGIRIMAN.SETTANGGAL(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("tanggal").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETTGL_TA(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tgl_ta").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETTGL_TD(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tgl_td").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETVOLUME(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("volume").toString()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_pengajuan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_PENGIRIMAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_pengiriman").toString());
            call.execute();
            
            sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}
	
	private List<Map> insertPengirimanBB_SAMPLING(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        
        try{
        	String sql = null;
            CallableStatement call = null;
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_PENGAJUAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_pengajuan").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETJUMLAH_SAMPLING(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("jumlah_sampling").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETTS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("ts").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETHGI(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("hgi").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETTM(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("tm").toString());
            call.execute();                
            
            sql = "{call BBTRXPENGIRIMAN.SETTANGGAL(?)}";
            call = con.prepareCall(sql);
            DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
            Date tgl = df.parse(map.get("tanggal").toString());
            call.setDate(1, new java.sql.Date(tgl.getTime()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETCV(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("cv").toString());
            call.execute();

            sql = "{call BBTRXPENGIRIMAN.SETID_PENGIRIMAN(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("id_pengiriman").toString());
            call.execute();            
            
            sql = "{call BBTRXPENGIRIMAN.SETASH(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("ash").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETID_SURVEYOR(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("id_surveyor").toString()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETVOLUME(?)}";
            call = con.prepareCall(sql);
            call.setBigDecimal(1, new BigDecimal(map.get("volume").toString()));
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETCREATEBY(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("user").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETAPP_ACCESS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("app_access").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETNO_SAMPLING(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("no_sampling").toString());
            call.execute();
            
            sql = "{call BBTRXPENGIRIMAN.SETSTATUS(?)}";
            call = con.prepareCall(sql);
            call.setString(1, map.get("status").toString());
            call.execute();
            
            try{
            	sql = "{call BBTRXPENGIRIMAN.SETFILE_SERTIFIKAT(?)}";
                call = con.prepareCall(sql);                
                File file = new File (map.get("file").toString());
                byte[] buffer = new byte[(int)file.length()];
                FileInputStream fis = new FileInputStream(file);
                call.setBinaryStream(1, fis, (int) file.length());
                call.execute();
                
                file.delete();
                
                sql = "{call BBTRXPENGIRIMAN.SETNO_SERTIFIKAT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_sertifikat").toString());
                call.execute();
            }catch (Exception e) {
				// TODO: handle exception
			}
            
            
            sql = "{? = call BBTRXPENGIRIMAN.INSERTPENGIRIMANBB(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            
            map.put("message", call.getString(1));    
            System.out.println(call.getString(1));
        }catch (Exception e) {
        	map.put("message", e.getMessage());    
        	e.printStackTrace();
		}
        lMap.add(map);
        return lMap;
	}
}
